package info.batcloud.fanli.core.repository;

import info.batcloud.fanli.core.entity.AllotTypeCommissionFee;
import info.batcloud.fanli.core.enums.CommissionAllotType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface AllotTypeCommissionFeeRepository extends JpaRepository<AllotTypeCommissionFee, String> {

    @Query(value = "select concat(?1,?2,?3,allot_type) as id, allot_type as allot_type, count(1) as order_num, " +
            "convert(sum(uco.commission_rate * co.estimate_commission_fee / 100), decimal(10,2)) as commission_fee " +
            "from user_commission_order uco left join commission_order co on uco.commission_order_id=co.id " +
            "where uco.user_id=?1 and co.create_time between ?2 and ?3 and co.status != 'INVALID' group by allot_type", nativeQuery = true)
    List<AllotTypeCommissionFee> statUserEstimateCommissionFeeByCreateTimeBetweenGroupByAllotType(long userId, Date startTime, Date endTime);


}
